﻿using Providers;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Services
{
    /// <summary>
    /// Enum representing export types for query results
    /// </summary>
    public enum QueryExportType
    {
        /// <summary>
        /// Export the query results as a comma seperated file
        /// </summary>
        CommaSeperated = 0,

        /// <summary>
        /// Export the query results as a tab seperated file
        /// </summary>
        TabSeperated = 1
    }

    /// <summary>
    /// Enum represening the style of export for a set of query results
    /// </summary>
    public enum QueryExportStyle
    {
        /// <summary>
        /// Each batch of results will be saved in its own file
        /// </summary>
        SeperateFiles = 0,

        /// <summary>
        /// All results will be saved in a single file with a header above each result set
        /// </summary>
        OneFileWithSeperators = 1,

        /// <summary>
        /// All results will be saved in a single file but there will be no header to seperate result sets. An extra column
        /// with the database and server name will be added
        /// </summary>
        OneFileContinuous = 2,

        /// <summary>
        /// Each batch of results will be saved in its own file and the file will be named after the database/server that
        /// the results belonged to
        /// </summary>
        SeperateFilesWithDatabaseName = 3
    }

    /// <summary>
    /// Manage Queries
    /// </summary>
    public class QueryService
        : ServiceBase<IQueryProvider>
    {
        /// <summary>
        /// Initialise Query service
        /// </summary>
        public QueryService()
        {
            Initialise();
        }

        /// <summary>
        /// Save a query under the specified name
        /// </summary>
        /// <param name="queryName">The name under which the query should be saved</param>
        /// <param name="queryToSave">The actual query to save</param>
        /// <param name="errorMsg">Any error messages generated during the save process</param>
        /// <returns>True if the save was successfull, otherwise false</returns>
        public bool SaveQuery(string queryName, Models.Query queryToSave, out string errorMsg)
        {
            return Provider.SaveText(queryName, queryToSave.QueryText, out errorMsg);
        }

        /// <summary>
        /// Load a query stored under the specified name
        /// </summary>
        /// <param name="queryName">The name of the query to load</param>
        /// <param name="errorMsg">Any error messages generated during the save process</param>
        /// <returns>A query if the load was successfull, otherwise null</returns>
        public Models.Query LoadQuery(string queryName, out string errorMsg)
        {
            Models.Query loadedQuery = new Models.Query(string.Empty, Models.Query.QueryType.Auto, 30);
            loadedQuery.QueryText = Provider.LoadText(queryName, out errorMsg);
            return loadedQuery;
        }

        /// <summary>
        /// Export a list of query's results
        /// </summary>
        /// <param name="fileName">The filename to export the queries to</param>
        /// <param name="resultsToExport">The list of databases whose results we will export</param>
        /// <param name="exportStyle">The style of export to use</param>
        /// <param name="seperateFiles">Whether or not to export the data into seperate files or all in one file</param>
        /// <param name="errorMsg">Any error messages</param>
        /// <returns>True if the export process completed successfully</returns>
        public bool ExportResults(string fileName, List<Models.Database> resultsToExport, QueryExportType exportType,
            QueryExportStyle exportStyle, out string errorMsg)
        {
            // By default assume no error and that the operation will be a success
            bool result = true;
            errorMsg = string.Empty;

            StringBuilder exportData = new StringBuilder();

            // If we are saving each result set in its own file then each file will be given a number
            int fileNum = 1;

            // Loop through each database and export the data
            foreach (Models.Database db in resultsToExport)
            {
                if ((db.Result != null) && (db.Result.Result != null) && (db.Result.Result is DataTable))
                {
                    // We have results and the results are a data table
                    DataTable dbResult = (DataTable)db.Result.Result;

                    // Convert the data table into a string and add it to the exportData variable
                    string databaseServerName = string.Format("{0} - {1}", db.DatabaseName, db.DatabaseServer.ServerName);
                    bool includeHeader = (exportStyle == QueryExportStyle.OneFileContinuous) ? false : true;
                    exportData.Append(ConvertDataTableToString(dbResult, exportType, includeHeader, databaseServerName));
                    exportData.AppendLine();
                    exportData.AppendLine();
                }

                if (exportStyle == QueryExportStyle.SeperateFiles)
                {
                    // If we are splitting the export up into one result set per file then save and clear out the data here
                    result = result && Provider.SaveNumberedText(fileName, fileNum, exportData.ToString(), out errorMsg);
                    fileNum++;
                    exportData.Clear();
                }
                else if (exportStyle == QueryExportStyle.SeperateFilesWithDatabaseName)
                {
                    // If we are splitting the export up imto one result set per file AND naming the file after the database
                    // ans server that they came from then save here and clear the results data out in ready for the next file
                    string dbFileName = Provider.GetDatabaseServerFileName(fileName, db);
                    result = result && Provider.SaveText(dbFileName, exportData.ToString(), out errorMsg);
                    exportData.Clear();
                }
            }

            // If we are saving all the data in one go then do so here
            if ((exportStyle != QueryExportStyle.SeperateFiles) &&
                (exportStyle != QueryExportStyle.SeperateFilesWithDatabaseName))
            {
                result = result && Provider.SaveText(fileName, exportData.ToString(), out errorMsg);
            }

            return result;
        }

        /// <summary>
        /// Create a provider of the type used by the service
        /// </summary>
        /// <returns>A provider</returns>
        protected override IQueryProvider CreateProvider()
        {
            return ProviderFactory.CreateQueryProvider();
        }

        /// <summary>
        /// Convert a data table to a string ready for the export process
        /// </summary>
        /// <param name="data">The data table to convert</param>
        /// <param name="exportType">Whether to convert to tab seperated or comma seperated</param>
        /// <param name="includeHeader">Whether or not to include a header row</param>
        /// <param name="databaseServerColText">If includeHeader is false then this text will be added as the first
        /// column for each row</param>
        /// <returns>The string representation of the data table</returns>
        private string ConvertDataTableToString(DataTable data, QueryExportType exportType, bool includeHeader, string databaseServerColText)
        {
            StringBuilder result = new StringBuilder();

            // Get what type of seperater we are using
            string colSep = (exportType == QueryExportType.CommaSeperated) ? "," : "\t";

            if (includeHeader)
            {
                // Add name of database and server
                result.AppendFormat("{0}\n\n", databaseServerColText);

                // We want to include a header row
                for (int headerColIndex = 0; headerColIndex < data.Columns.Count; headerColIndex++)
                {
                    // If this is the last column then we do not need to add a trailing comma/tab
                    if (headerColIndex == data.Columns.Count - 1)
                    {
                        result.AppendFormat("\"{0}\"", data.Columns[headerColIndex].Caption);
                    }
                    else
                    {
                        result.AppendFormat("\"{0}\"{1}", data.Columns[headerColIndex].Caption, colSep);
                    }
                }

                // Add a cr to the end
                result.AppendLine();
            }

            // Now do the actual data
            foreach (DataRow row in data.Rows)
            {
                // If include header is false then add the server and database name as the first column
                if (!includeHeader)
                {
                    result.AppendFormat("\"{0}\"{1}", databaseServerColText, colSep);
                }

                // Add this row to the result
                for (int colIndex = 0; colIndex < data.Columns.Count; colIndex++)
                {
                    // If this is the last column then we do not need to add a trailing comma/tab
                    if (colIndex == data.Columns.Count - 1)
                    {
                        result.AppendFormat("\"{0}\"", row[colIndex].ToString());
                    }
                    else
                    {
                        result.AppendFormat("\"{0}\"{1}", row[colIndex].ToString(), colSep);
                    }
                }

                // Add a cr to the end
                result.AppendLine();
            }

            return result.ToString();
        }
    }
}
